package com.lsmgc.cms.dao.poster;

import com.lsmgc.cms.entity.poster.Poster;
import com.lsmgc.cms.entity.poster.PosterQuery;
import com.lsmgc.cms.util.jdbc.C3P0Utils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.lang3.StringUtils;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class PosterDao {
    public int addPoster(Poster poster) throws SQLException {
        QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
        String sql = "insert into poster(name,img,link,add_time) value(?,?,?,?)";
        return runner.update(sql, poster.getName(), poster.getImg(), poster.getLink(), poster.getAddTime());
    }
    public List<Poster> getPosterListPage(PosterQuery posterQuery) throws SQLException {
        List<Object> param = new ArrayList<>();
        QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
        StringBuilder sql = new StringBuilder();
        sql.append("select * from poster");
        if (StringUtils.isNotBlank(posterQuery.getName())) {
            sql.append(" where");
            sql.append(" name like ?");
            param.add("%" + posterQuery.getName() + "%");
        }
        sql.append(" order by id desc");
        sql.append(" limit ?,?");
        param.add(posterQuery.getStartRow());
        param.add(posterQuery.getPageSize());
        System.out.println("sql: " + sql.toString());
        return qr.query(sql.toString(), new BeanListHandler<>(Poster.class), param.toArray());
    }

    public Long getPosterListPageCount(PosterQuery posterQuery) throws SQLException {
        List<Object> param = new ArrayList<>();
        QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
        StringBuilder sql = new StringBuilder();
        sql.append("select count(1) num from poster");
        if (StringUtils.isNotBlank(posterQuery.getName())) {
            sql.append(" where");
            sql.append(" name like ?");
            param.add("%" + posterQuery.getName() + "%");
        }
        System.out.println("sql: " + sql.toString());
        return qr.query(sql.toString(), new ScalarHandler<>("num"), param.toArray());
    }

    public Poster getPosterById(Long id) throws SQLException {
        QueryRunner qr = new QueryRunner((C3P0Utils.getDataSource()));
        String sql = "select * from poster where id=?";
        return qr.query(sql, new BeanHandler<>(Poster.class), id);
    }

    public int updatePosterById(Poster poster) throws SQLException {
        QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
        String sql = "update poster set name=?,img=?,link=? where id=?";
        return runner.update(sql, poster.getName(), poster.getImg(), poster.getLink(), poster.getId());
    }

    public int deletePosterById(Long id) throws SQLException {
        QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
        String sql = "delete from poster where id=?";
        return runner.update(sql, id);
    }
}
